package groep3.ssc.controllers;

import groep3.ssc.model.Scheme;
import groep3.ssc.model.SchemeEntry;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbController extends SQLiteOpenHelper
{
    private static final String DATABASE_NAME = "speedskatingcoach.db";

    private static final int VERSION = 1;
    private static final String TABLE_SCHEMES = "schemes";
    private static final String SCHEMES_ID = "_id";
    private static final String SCHEMES_NAME = "name";
    private static final String SCHEMES_ABBRV = "abbriviation";

    private static final String TABLE_SCHEME_ENTRY = "schemeEntry";
    private static final String SCHEME_ENTRY_ID = "_id";
    private static final String SCHEME_ENTRY_LAPNR = "lapnr";
    private static final String SCHEME_ENTRY_SCHEME_ID = "scheme_id";
    private static final String SCHEME_ENTRY_LAPTIME = "laptime";

    private static DbController instance;

    private DbController(Context context)
    {
        super(context, DATABASE_NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        //@formatter:off
        db.execSQL("CREATE TABLE " + TABLE_SCHEMES + " (" + SCHEMES_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + SCHEMES_NAME
                + " TEXT, " + SCHEMES_ABBRV + " TEXT )");

        db.execSQL("CREATE TABLE " + TABLE_SCHEME_ENTRY + " (" + SCHEME_ENTRY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + SCHEME_ENTRY_LAPNR + " INTEGER, " + SCHEME_ENTRY_SCHEME_ID + " INTEGER, " + SCHEME_ENTRY_LAPTIME + " INTEGER, "
                + "FOREIGN KEY (" + SCHEME_ENTRY_SCHEME_ID + ") REFERENCES " + TABLE_SCHEMES + "(" + SCHEMES_ID + "))");

        //@formatter:on

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        Log.w(DbController.class.getName(), "Upgrading database from version " + oldVersion + " to " + newVersion
                + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SCHEMES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SCHEME_ENTRY);
        onCreate(db);
    }

    public void addScheme(Scheme scheme)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(SCHEMES_NAME, scheme.getTitle());
        values.put(SCHEMES_ABBRV, scheme.getAbbreviation());

        long insertId = db.insert(TABLE_SCHEMES, null, values);
        scheme.setId((int) insertId);
        db.close();

        addSchemeEntries(scheme.getEntries(), insertId);

    }

    private void addSchemeEntries(ArrayList<SchemeEntry> entries, long insertId)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        for (SchemeEntry schemeEntry : entries)
        {
            ContentValues values = new ContentValues();
            values.put(SCHEME_ENTRY_LAPNR, schemeEntry.getLapNumber());
            values.put(SCHEME_ENTRY_SCHEME_ID, insertId);
            values.put(SCHEME_ENTRY_LAPTIME, schemeEntry.getLapTime());
            long entryId = db.insert(TABLE_SCHEME_ENTRY, null, values);
            schemeEntry.setId(entryId);
        }
        db.close();
    }

    public void updateScheme(Scheme scheme)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(SCHEMES_NAME, scheme.getTitle());
        values.put(SCHEMES_ABBRV, scheme.getAbbreviation());

        db.update(TABLE_SCHEMES, values, SCHEMES_ID + " = ?", new String[] { String.valueOf(scheme.getId()) });
        db.close();
        updateSchemeEntries(scheme);

    }

    private void updateSchemeEntries(Scheme scheme)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        for (SchemeEntry schemeEntry : scheme.getEntries())
        {
            ContentValues values = new ContentValues();
            values.put(SCHEME_ENTRY_LAPNR, schemeEntry.getLapNumber());
            values.put(SCHEME_ENTRY_LAPTIME, schemeEntry.getLapTime());
            values.put(SCHEME_ENTRY_SCHEME_ID, scheme.getId());
            if (schemeEntry.getId() == 0)
            {
                long entryId = db.insert(TABLE_SCHEME_ENTRY, null, values);
                schemeEntry.setId(entryId);
            } else
            {
                db.update(TABLE_SCHEME_ENTRY, values, SCHEME_ENTRY_ID + " = ?", new String[] { String.valueOf(schemeEntry.getId()) });
            }
        }
        db.close();
    }

    public Scheme getScheme(long id)
    {
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_SCHEMES + " WHERE " + SCHEMES_ID + " = " + id;

        Cursor cursor = db.rawQuery(query, null);
        Scheme scheme = null;

        if (cursor.moveToFirst())
        {
            scheme = new Scheme(cursor.getString(1), cursor.getString(2));
            scheme.setId(cursor.getInt(0));
        }
        if (scheme != null)
        {
            scheme.setEntries(this.getSchemeEntries(id));
        }
        db.close();
        return scheme;
    }

    public ArrayList<Integer> getIdListOfSchemes()
    {
        ArrayList<Integer> ids = new ArrayList<Integer>();
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT " + SCHEMES_ID + " FROM " + TABLE_SCHEMES;
        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst())
        {
            do
            {
                ids.add(cursor.getInt(0));
            } while (cursor.moveToNext());
        }
        db.close();
        return ids;
    }

    public ArrayList<SchemeEntry> getSchemeEntries(long schemeId)
    {
        ArrayList<SchemeEntry> listOfSchemes = new ArrayList<SchemeEntry>();
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_SCHEME_ENTRY + " WHERE " + SCHEME_ENTRY_SCHEME_ID + " = " + schemeId + " ORDER BY "
                + SCHEME_ENTRY_LAPNR + " ASC";
        SchemeEntry schemeEntry;
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst())
        {
            long totalTime = 0;
            do
            {
                schemeEntry = new SchemeEntry(cursor.getInt(1), cursor.getInt(3));
                schemeEntry.setId(cursor.getInt(0));
                totalTime += cursor.getLong(3);
                schemeEntry.setTotalTime(totalTime);
                listOfSchemes.add(schemeEntry);
            } while (cursor.moveToNext());
        }
        db.close();
        return listOfSchemes;
    }

    public void removeEntry(SchemeEntry entry)
    {
        // TODO Auto-generated method stub

    }

    public static DbController getInstance()
    {
        return instance;
    }

    public static DbController create(Context context)
    {
        if (instance == null)
        {
            instance = new DbController(context);
        }
        return instance;
    }

}
